﻿Imports System.Data
Imports System.Data.SqlClient
Public Class AbastecimentoDAL
    Public Sub New()
        'contrutor padrão
    End Sub
    Dim cmd As SqlCommand
    Dim objD As Dados
#Region "Métodos"
    Public Sub inserir(vei_codigo As Integer, aba_km As Integer, aba_valor As Double, aba_data As Date)
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "exec sp_Inserir_Abastecimento @vei_codigo,@aba_km,@aba_valor,@aba_data"
            cmd.Parameters.Add(New SqlParameter("@vei_codigo", SqlDbType.Int)).Value = vei_codigo
            cmd.Parameters.Add(New SqlParameter("@aba_km", SqlDbType.Int)).Value = aba_km
            cmd.Parameters.Add(New SqlParameter("@aba_valor", SqlDbType.Money)).Value = aba_valor
            cmd.Parameters.Add(New SqlParameter("@aba_data", SqlDbType.Date)).Value = aba_data
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub
    Public Sub alterar(vei_codigo As Integer, aba_km As Integer, aba_valor As Double, aba_data As Date, aba_codigo As Integer)
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "exec sp_Alterar_Abastecimento @vei_codigo,@aba_km,@aba_valor,@aba_data,@aba_codigo"
            cmd.Parameters.Add(New SqlParameter("@vei_codigo", SqlDbType.Int)).Value = vei_codigo
            cmd.Parameters.Add(New SqlParameter("@aba_km", SqlDbType.Int)).Value = aba_km
            cmd.Parameters.Add(New SqlParameter("@aba_valor", SqlDbType.Money)).Value = aba_valor
            cmd.Parameters.Add(New SqlParameter("@aba_data", SqlDbType.Date)).Value = aba_data
            cmd.Parameters.Add(New SqlParameter("@aba_codigo", SqlDbType.Int)).Value = aba_codigo
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub
    Public Sub excluir(aba_codigo As Integer)
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "exec sp_Deleta_Abastecimento @aba_codigo"
            cmd.Parameters.Add(New SqlParameter("@aba_codigo", SqlDbType.Int)).Value = aba_codigo
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub
    Function localizar(ByVal descricao As String) As DataTable
        Dim tab As DataTable = Nothing
        Try
            cmd = New SqlCommand
            objD = New Dados
            If (descricao = "ultimo") Then
                cmd.CommandText = "SELECT aba_codigo = max(aba_codigo) FROM abastecimento "
            ElseIf (descricao = "primeiro") Then
                cmd.CommandText = "SELECT aba_codigo = min(aba_codigo) FROM abastecimento "
            Else
                'Comando para localizar vários registros no banco
                cmd.CommandText = "SELECT * FROM vw_veiculos_abastecimento " +
                                  "WHERE mod_nome like @descricao ORDER BY aba_data"
                cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" + descricao + "%"
            End If
            'Execução do comando
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function
    Function localizarProxAnterior(ByVal descricao As String, ByVal codigo As Integer) As DataTable
        Dim tab As DataTable = Nothing
        Try
            cmd = New SqlCommand
            objD = New Dados
            If (descricao = "proximo") Then
                cmd.CommandText = "SELECT aba_codigo FROM abastecimento WHERE aba_codigo = (SELECT MIN(aba_codigo) FROM " +
                                    "abastecimento WHERE aba_codigo > @codigo)"

            ElseIf (descricao = "anterior") Then
                cmd.CommandText = "SELECT aba_codigo FROM abastecimento  WHERE aba_codigo = (SELECT MAX(aba_codigo) FROM " +
                                    "abastecimento WHERE aba_codigo < @codigo)"
            End If
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Function localizar(ByVal codigo As Integer) As DataTable
        Dim tab As DataTable = Nothing
        Try
            cmd = New SqlCommand
            objD = New Dados
            'Comando para localizar vários registros no banco
            cmd.CommandText = "SELECT * FROM abastecimento WHERE aba_codigo = @codigo"
            'Parâmetro de entrada 
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            'Execução do comando
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function
    Public Function localizarAbasVeiculo(codigo As Integer) As DataTable
        Dim tab As DataTable = Nothing
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "SELECT aba_data,aba_km,aba_valor from abastecimento where vei_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function
    Public Function localizar(ByVal dt_inicio As Date, ByVal dt_final As Date) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "SELECT a.aba_codigo,m.mod_nome,a.aba_valor,a.aba_data " +
                                    "FROM	abastecimento a " +
                                    "INNER JOIN veiculos v ON v.vei_codigo = a.vei_codigo " +
                                    "INNER JOIN modelos m ON m.mod_codigo = v.mod_codigo " +
                                    "WHERE a.aba_data between @dt_inicio and @dt_final " +
                                    "ORDER BY a.aba_data"
            cmd.Parameters.Add(New SqlParameter("@dt_inicio", SqlDbType.Date)).Value = dt_inicio
            cmd.Parameters.Add(New SqlParameter("@dt_final", SqlDbType.Date)).Value = dt_final
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function
#End Region
End Class
